﻿
//----------------------------------------------------------------------
// <copyright file="DataBase.cs" company="O-RID">
// Copyright (c)2010 O-RID Co.,Ltd.
// </copyright>
// <author>lidawei</author>
// <summary> 
// 作成日： 2010/10/18
// </summary>
//----------------------------------------------------------------------

using System.Data;

namespace Orid.Management.SOHO.Book.ServiceLibrary.Data
{
    using System;
    using System.Data.SqlClient;
    using System.Text;
    using System.Configuration;

    public class DataBase : IDisposable
    {


        public DataBase()
        {
            connection = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnStr"].ConnectionString);
            connection.Open();
        }


        /// <summary>
        /// Adds the schedule.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="accountID">The account ID.</param>
        /// <param name="dateFrom">The date from.</param>
        /// <param name="dateTo">The date to.</param>
        /// <returns></returns>
        public static bool AddSchedule(string operatorID,int accountID, DateTime dateFrom,DateTime dateTo)
        {
            using (DataBase db = new DataBase())
            {
                bool retbool = false; ;
                db.BeginTransaction();
                db.DeleteSchedule(operatorID ,dateFrom,dateTo);
                if (db.ScheduleDateExists(operatorID, dateFrom,dateTo))
                {
                    db.DeleteSchedule(operatorID, dateFrom, dateTo);
                }

                retbool = db.InsertSchedule(operatorID,accountID, dateFrom, dateTo);

                db.CommitTransaction();

                return retbool;
            }
        }


        /// <summary>
        /// Removes the schedule.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="dateFrom">The date from.</param>
        /// <param name="dateTo">The date to.</param>
        /// <returns></returns>
        public static bool RemoveSchedule(string operatorID,DateTime dateFrom, DateTime dateTo)
        {
            using (DataBase db = new DataBase( ))
            {
                db.BeginTransaction();
                bool success = db.DeleteSchedule(operatorID,  dateFrom,   dateTo)
                ;
                db.CommitTransaction();
                return success;
            }
        }

        

       #region Disposable / Finalize パターンの実装

        private bool disposed = false;

        ~DataBase()
        {
            Dispose(false);
        }

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        protected virtual void Dispose(bool disposing)
        {
            if (!this.disposed)
            {
                Close();
            }
            this.disposed = true;
        }
        

        #endregion //Disposable / Finalize パターンの実装

        


        /// <summary>
        /// Schedules the date exists.
        /// </summary>
        /// <param name="operatorID"></param>
        /// <param name="dateFrom">The date from.</param>
        /// <param name="dateTo">The date to.</param>
        /// <returns></returns>
        private bool ScheduleDateExists(string operatorID, DateTime dateFrom, DateTime dateTo)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("    SELECT *");
            sql.Append("    FROM  [BookList]");
            sql.Append("    WHERE [OperatorID] = @OperatorID ");
            sql.Append("    AND [BookDateFrom] =@BookDateFrom ");
            sql.Append("    AND [BookDateTo] = @BookDateTo ");

            using (SqlCommand cmd = new SqlCommand(sql.ToString(), this.connection, this.transaction))
            {
                cmd.CommandText = sql.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = transaction;
                cmd.Parameters.AddWithValue("@OperatorID", operatorID);
                cmd.Parameters.AddWithValue("@BookDateFrom", dateFrom);
                cmd.Parameters.AddWithValue("@BookDateTo", dateTo);

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    return reader.HasRows;
                }
            }
        }


        /// <summary>
        /// Inserts the schedule.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="accountID">The account ID.</param>
        /// <param name="dateFrom">The date Bookfrom.</param>
        /// <param name="dateTo">The date Bookto.</param>
        /// <returns></returns>
        private bool InsertSchedule(string operatorID,int accountID, DateTime dateFrom,DateTime dateTo)
        {
              StringBuilder sql = new StringBuilder();
                sql.Append("   INSERT INTO  [BookList] ");
                sql.Append(" (  [OperatorID] ");
                sql.Append(",   [AccountID] ");
                sql.Append(",   [BookDateFrom] ");
                sql.Append(",   [BookDateTo] ");
                sql.Append(",   [UpdateTime]) ");
                sql.Append("    VALUES ");
                sql.Append("(   @OperatorID ");
                sql.Append(",   @AccountID ");
                sql.Append(",   @BookDateFrom ");
                sql.Append(",   @BookDateTo ");
                sql.Append(",   @UpdateTime) ");

                using (SqlCommand cmd = new SqlCommand(sql.ToString(), this.connection, this.transaction))
            {
                cmd.CommandText = sql.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = transaction;

                cmd.Parameters.AddWithValue("@OperatorID", operatorID);
                cmd.Parameters.AddWithValue("@AccountID", accountID);
                cmd.Parameters.AddWithValue("@BookDateFrom", dateFrom);
                cmd.Parameters.AddWithValue("@BookDateTo", dateTo);
                cmd.Parameters.AddWithValue("@UpdateTime", DateTime.Now);
                return (cmd.ExecuteNonQuery() == 1);
            }
        }

        /// <summary>
        /// Deletes the schedule.
        /// </summary>
        /// <param name="operatorID">The operator ID.</param>
        /// <param name="dateFrom">The date from.</param>
        /// <param name="dateTo">The date to.</param>
        /// <returns></returns>
        private bool DeleteSchedule(string operatorID, DateTime dateFrom, DateTime dateTo)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("    INSERT INTO  [BookListHistory] ");
            sql.Append("    ([OperatorID] ");
            sql.Append("    ,[AccountID] ");
            sql.Append("    ,[BookDateFrom] ");
            sql.Append("    ,[BookDateTo] ");
            sql.Append("    ,[UpdateTime]) ");
            sql.Append("    SELECT [OperatorID] ");
            sql.Append("    ,[AccountID] ");
            sql.Append("    ,[BookDateFrom] ");
            sql.Append("    ,[BookDateTo] ");
            sql.Append("    ,[UpdateTime] ");
            sql.Append("    FROM  [BookList] ");
            sql.Append("    WHERE [OperatorID] = @OperatorID ");
            sql.Append("    AND [BookDateFrom] =@BookDateFrom ");
            sql.Append("    AND [BookDateTo] = @BookDateTo ");
            sql.Append("    ; ");
            sql.Append("    DELETE ");
            sql.Append("    FROM  [BookList]");
            sql.Append("    WHERE [OperatorID] = @OperatorID ");
            sql.Append("    AND [BookDateFrom] =@BookDateFrom ");
            sql.Append("    AND [BookDateTo] = @BookDateTo ");

            using (SqlCommand cmd = new SqlCommand(sql.ToString(), this.connection, this.transaction))
            {
                cmd.CommandText = sql.ToString();
                cmd.CommandType = CommandType.Text;
                cmd.Transaction = transaction;
                cmd.Parameters.AddWithValue("@OperatorID", operatorID);
                cmd.Parameters.AddWithValue("@BookDateFrom", dateFrom);
                cmd.Parameters.AddWithValue("@BookDateTo", dateTo);

                return (cmd.ExecuteNonQuery() >= 1);
            }
        } 

        #region トランザクション処理

        private SqlConnection connection;
        private SqlTransaction transaction;

        public void BeginTransaction()
        {
            if (this.transaction == null)
            {
                this.transaction = this.connection.BeginTransaction();
            }
        }

        public void CommitTransaction()
        {
            if (this.transaction != null)
            {
                this.transaction.Commit();
                this.transaction = null;
            }
        }

        public void RollbackTransaction()
        {
            if (this.transaction != null)
            {
                this.transaction.Rollback();
                this.transaction = null;
            }
        }

        public void Close()
        {
            RollbackTransaction();
            if (this.connection != null)
            {
                this.connection.Close();
                this.connection = null;
            }
        }
        #endregion トランザクション処理
    }
}

 
